#!/usr/bin/env python
# -*- coding: UTF-8 -*-
"""
@Project ：tools 
@File ：成品合同匹配.py
@IDE  ：PyCharm 
@Author ：李涵彬
@Date ：2024/11/18 下午4:49 
"""

"""
需要操作合同表，物料表，减宽量配置表，以优充次配置表

1.读取四个excel表格，表格可能是csv、xls、xlsx格式文件
2.逐行遍历物料表，使用“出钢标记”在合同表中进行数据匹配，如果能够匹配到数据，则进行3、4，如果无法匹配到数据，则跳过3、4，从5开始执行
3.如果物料表中“推荐轧宽”列存在值，则解析该单元格的两个数值，分别作为范围最小值和范围最大值；使用该范围与合同表中的“轧宽”进行匹配，如果该物料轧宽在范围内，则该合同号符合要求，添加这个合同号
4.如果物料表中“推荐轧宽”列不存在值，则通过减宽量配置表解析计算“推荐轧宽”范围，需要首先获取合同表中的“全程途径码”的第9、第10位，在减宽量配置表中匹配“产线”列，“宽度小值”作为范围最小值，“宽度大值”作为范围最大值，之后使用该范围与合同表中的“轧宽”+对应“减宽量”进行匹配，如果在范围内，则添加这个合同号
5.如果使用“出钢标记”在合同表中进行数据匹配，不能够匹配到数据，则在以优充次配置表中，匹配“(材料)出钢标记”列，将对应的“(合同)出钢标记”作为“出钢标记”进行二次匹配，如果“钢级代码”列存在值，则使用“出钢标记”和“钢级代码”同时进行匹配，如果不存在值，则解析“宽度上限”“宽度下限”“厚度上限”“厚度下限”，使用这两个范围，分别对“轧宽”“轧厚”进行范围判定，符合范围内的，进行合同号添加
6.构建dataframe格式的result变量，第一列为材料号，第二列为出钢标记，之后，匹配到的合同号依次填充
7.最后将result保存到桌面
"""

import tkinter as tk
from tkinter import filedialog, messagebox, ttk
import pandas as pd
import ast
import logging
from pathlib import Path
from typing import List, Dict, Union, Optional
from threading import Thread

# 日志配置
LOG_DIR = Path(__file__).parent / 'logs'
LOG_DIR.mkdir(exist_ok=True)
LOG_FILE = LOG_DIR / 'app.log'

logging.basicConfig(
	level=logging.INFO,
	format='%(asctime)s - %(levelname)s - %(message)s',
	handlers=[logging.FileHandler(LOG_FILE), logging.StreamHandler()]
)

# 配置文件路径
CONFIG_DIR = Path(__file__).parent / 'config'
REDUCTION_WIDTH_CONFIG_PATH = CONFIG_DIR / '热轧产线减宽量配置表.xlsx'
SUBSTITUTE_CONFIG_PATH = CONFIG_DIR / '以优充次配置表.xlsx'


def read_excel_file(file_path: Union[str, Path], usecols: Optional[List[str]] = None) -> pd.DataFrame:
	"""
	读取 Excel 文件，并处理可能的异常。
	:param file_path: 文件路径
	:param usecols: 需要加载的列（可选）
	:return: DataFrame
	"""
	try:
		logging.info(f"读取文件: {file_path}")
		return pd.read_excel(file_path, usecols=usecols)
	except Exception as e:
		logging.error(f"无法读取文件 {file_path}: {e}")
		raise


def match_contracts_by_width(matched_contracts: pd.DataFrame, min_width: float, max_width: float) -> pd.DataFrame:
	"""
	通过轧宽范围匹配合同。
	:param matched_contracts: 待匹配的合同数据
	:param min_width: 最小宽度
	:param max_width: 最大宽度
	:return: 筛选后的合同数据
	"""
	try:
		return matched_contracts[(matched_contracts['轧宽'] >= min_width) & (matched_contracts['轧宽'] <= max_width)]
	except Exception as e:
		logging.error(f"筛选合同时发生错误: {e}, 范围: [{min_width}, {max_width}]")
		raise


def match_contracts_by_reduction_width(material_row_width: int, matched_contracts: pd.DataFrame,
									   reduction_width_config_df: pd.DataFrame) -> pd.DataFrame:
	"""
	使用减宽量配置表匹配合同。
	:param material_row_width: 材料宽度
	:param matched_contracts: 待匹配合同数据
	:param reduction_width_config_df: 减宽量配置表
	:return: 筛选后的合同数据
	"""
	reduction_config_group = reduction_width_config_df.groupby('产线')
	result_rows = []

	for _, row in matched_contracts.iterrows():
		prod_line = row['全程途径码'][8:10]
		if prod_line not in reduction_config_group.groups:
			logging.warning(f"未找到减宽量配置：产线 {prod_line}")
			continue

		configs = reduction_config_group.get_group(prod_line)
		for _, sub_row in configs.iterrows():
			if sub_row['宽度小值'] <= row['轧宽'] <= sub_row['宽度大值']:
				min_width, max_width = row['轧宽'], row['轧宽'] + sub_row['减宽量']
				if min_width <= material_row_width <= max_width:
					result_rows.append(row)
					break

	return pd.DataFrame(result_rows)


def substitute_contracts(material_row_width: int, material_drawing_code: str, substitute_config_df: pd.DataFrame,
						 reduction_width_config_df: pd.DataFrame, contracts_df: pd.DataFrame) -> List[str]:
	"""
	使用以优充次配置表匹配合同。
	:param material_row_width: 材料宽度
	:param material_drawing_code: 材料出钢标记
	:param substitute_config_df: 以优充次配置表
	:param reduction_width_config_df: 减宽量配置表
	:param contracts_df: 合同数据
	:return: 匹配到的合同号列表
	"""
	substitute_match = substitute_config_df[substitute_config_df['(材料)出钢标记'] == material_drawing_code]
	if substitute_match.empty:
		return []

	matched_contracts_res = pd.DataFrame(columns=contracts_df.columns)

	for _, row in substitute_match.iterrows():
		logging.info(f"匹配以优充次配置：{row['(合同)出钢标记']}")

		substitute_drawing_code = row['(合同)出钢标记']
		substitute_contracts = contracts_df[contracts_df['出钢标记'] == substitute_drawing_code]

		if substitute_contracts.empty:
			logging.warning(f"未找到以优充次匹配的合同：{substitute_drawing_code}")
			continue

		if row['钢级代码']:
			substitute_contracts = substitute_contracts[substitute_contracts['钢级代码'] == row['钢级代码']]

		min_width, max_width, min_thickness, max_thickness = row[
			['宽度下限', '宽度上限', '厚度下限', '厚度上限']]
		substitute_contracts = substitute_contracts[
			(substitute_contracts['轧宽'] >= min_width) & (substitute_contracts['轧宽'] <= max_width) &
			(substitute_contracts['轧厚'] >= min_thickness) & (substitute_contracts['轧厚'] <= max_thickness)
			]

		substitute_contracts = match_contracts_by_reduction_width(material_row_width, substitute_contracts,
																  reduction_width_config_df)

		matched_contracts_res = pd.concat([matched_contracts_res, substitute_contracts], ignore_index=True)

	return matched_contracts_res['合同号'].dropna().unique().tolist()


def process_materials(materials_df: pd.DataFrame, contracts_df: pd.DataFrame, reduction_width_config_df: pd.DataFrame,
					  substitute_config_df: pd.DataFrame, progress_var: tk.DoubleVar) -> pd.DataFrame:
	"""
	处理物料数据并匹配合同号。
	"""
	result = []
	total_rows = len(materials_df)

	for index, material_row in materials_df.iterrows():
		material_drawing_code = material_row['出钢标记']
		material_number = material_row['材料号']
		material_recommend_width = material_row.get('推荐轧宽')

		logging.info(f"处理物料：{index + 1}/{total_rows}，材料号：{material_number}")
		contract_numbers = []

		try:
			matched_contracts = contracts_df[contracts_df['出钢标记'] == material_drawing_code]

			if not matched_contracts.empty:
				if material_recommend_width != '' and material_recommend_width != ' ':
					min_width, max_width = ast.literal_eval(material_recommend_width)
					matched_contracts = match_contracts_by_width(matched_contracts, min_width, max_width)
				else:
					matched_contracts = match_contracts_by_reduction_width(material_row['宽(mm)'], matched_contracts,
																		   reduction_width_config_df)
				contract_numbers = matched_contracts['合同号'].dropna().unique().tolist()

			# 如果未找到匹配合同，尝试以优充次逻辑
			if not contract_numbers:
				contract_numbers = substitute_contracts(
					material_row['宽(mm)'], material_drawing_code, substitute_config_df, reduction_width_config_df,
					contracts_df
				)

			res_contract_numbers = []
			for contract_number in contract_numbers:
				contract_row = contracts_df[contracts_df['合同号'] == contract_number]
				min_weight = float(contract_row['订货重量单件最小值'])
				max_weight = float(contract_row['订货重量单件最大值'])
				for i in range(1, 4):
					sub_min_weight = i * min_weight
					sub_max_weight = i * max_weight
					if sub_min_weight <= material_row['重量(t)'] <= sub_max_weight:
						res_contract_numbers.append(contract_number)
						break

		except Exception as e:
			logging.error(f"处理材料号 {material_number} 时发生错误: {e}")
			continue

		result.append({
			'材料号': material_number,
			'出钢标记': material_drawing_code,
			'合同号': ','.join(res_contract_numbers) if res_contract_numbers else None
		})

		# 更新进度条
		if index % (total_rows // 20) == 0:
			progress_var.set(index / total_rows)

	return pd.DataFrame(result)


def clean_input(value: str) -> str:
	"""
	清洗输入字符串，去除非标准字符。
	:param value: 原始值
	:return: 清洗后的值
	"""
	if isinstance(value, str):
		return value.strip().replace('\n', '').replace('\r', '')
	return value


def save_result_to_excel(result_df: pd.DataFrame, file_path: Union[str, Path]):
	"""
	保存结果到 Excel 文件。
	:param result_df: 结果 DataFrame
	:param file_path: 保存路径
	"""
	result_df.to_excel(file_path, index=False)
	logging.info(f"结果已保存到 {file_path}")


def main(contract_path: Union[str, Path], material_path: Union[str, Path],
		 reduction_width_config_path: Union[str, Path], substitute_config_path: Union[str, Path],
		 progress_var: tk.DoubleVar):
	"""
	主处理函数。
	"""
	try:
		contracts_df = read_excel_file(contract_path)
		materials_df = read_excel_file(material_path)
		reduction_width_config_df = read_excel_file(reduction_width_config_path)
		substitute_config_df = read_excel_file(substitute_config_path)

		result_df = process_materials(materials_df, contracts_df, reduction_width_config_df, substitute_config_df,
									  progress_var)

		split_contract_columns = result_df['合同号'].str.split(',', expand=True)
		max_columns = split_contract_columns.shape[1]
		for i in range(max_columns):
			result_df[f'合同号{i + 1}'] = split_contract_columns[i]

		result_path = Path.home() / 'Desktop' / 'result.xlsx'
		save_result_to_excel(result_df, result_path)
		messagebox.showinfo("完成", f"处理完成，结果已保存到：{result_path}")

	except Exception as e:
		logging.error(f"处理失败: {e}")
		messagebox.showerror("错误", f"处理失败: {e}")


def browse_file() -> str:
	"""
	打开文件选择对话框并返回文件路径。
	:return: 文件路径
	"""
	file_path = filedialog.askopenfilename(filetypes=[("Excel 文件", "*.xlsx"), ("所有文件", "*.*")])
	return file_path


def start_processing(contract_path: str, material_path: str, progress_var: tk.DoubleVar):
	"""
	启动数据处理的线程。
	:param contract_path: 合同文件路径
	:param material_path: 物料文件路径
	:param progress_var: 进度变量
	"""
	if not contract_path or not material_path:
		messagebox.showwarning("警告", "请先选择合同和物料文件！")
		return

	progress_var.set(0.0)

	thread = Thread(
		target=main,
		args=(contract_path, material_path, REDUCTION_WIDTH_CONFIG_PATH, SUBSTITUTE_CONFIG_PATH, progress_var),
		daemon=True
	)
	thread.start()


def create_gui():
	"""
	创建主界面。
	"""
	root = tk.Tk()
	root.title("合同匹配工具")
	root.geometry("900x300")
	font_size = 14

	# 文件选择
	contract_path_var = tk.StringVar()
	material_path_var = tk.StringVar()

	ttk.Label(root, font=("Arial", font_size), text="选择合同文件：").grid(row=0, column=0, padx=10, pady=10,
																		  sticky=tk.W)
	ttk.Entry(root, font=("Arial", font_size), textvariable=contract_path_var, width=50).grid(row=0, column=1, padx=10)
	ttk.Button(root, text="浏览", command=lambda: contract_path_var.set(browse_file())).grid(row=0, column=2, padx=10)

	ttk.Label(root, font=("Arial", font_size), text="选择物料文件：").grid(row=1, column=0, padx=10, pady=10,
																		  sticky=tk.W)
	ttk.Entry(root, font=("Arial", font_size), textvariable=material_path_var, width=50).grid(row=1, column=1, padx=10)
	ttk.Button(root, text="浏览", command=lambda: material_path_var.set(browse_file())).grid(row=1, column=2, padx=10)

	# 进度条
	progress_var = tk.DoubleVar()
	ttk.Label(root, font=("Arial", font_size), text="处理进度：").grid(row=2, column=0, padx=10, pady=10, sticky=tk.W)
	progress_bar = ttk.Progressbar(root, variable=progress_var, maximum=1.0)
	progress_bar.grid(row=2, column=1, columnspan=2, padx=10, pady=10, sticky=tk.W + tk.E)

	# 启动按钮
	ttk.Button(
		root, text="开始处理",
		command=lambda: start_processing(contract_path_var.get(), material_path_var.get(), progress_var)
	).grid(row=3, column=1, pady=20)

	root.mainloop()


if __name__ == "__main__":
	# 检查配置文件是否存在
	if not REDUCTION_WIDTH_CONFIG_PATH.exists() or not SUBSTITUTE_CONFIG_PATH.exists():
		logging.error("缺少配置文件，请检查 'config' 文件夹中的配置。")
		messagebox.showerror("错误", "缺少配置文件，请检查 'config' 文件夹。")
	else:
		create_gui()
